package cn.yangliu.poi.tools;

import cn.yangliu.comm.CommConst;
import cn.yangliu.comm.tools.DateUtils;
import cn.yangliu.comm.tools.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * excel文件工具
 */
public class ExcelUtils {

    private static final Logger logger = LoggerFactory.getLogger(ExcelUtils.class);

    public final static String EXCEL_XLS = ".xls";
    public final static String EXCEL_XLSX = ".xlsx";

    /**
     * 从excel文件中读取标题行
     *
     * @return list<标题>
     */
    public static final List<String> readHead(Sheet sheet) {
        List<String> result = new ArrayList<>();
        Row row = sheet.getRow(0);
        for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
            Cell cell = row.getCell(i);
            String content = checkHeadType(i, cell);
            result.add(content);
        }
        return result;
    }

    /**
     * 标题行必须为字符
     */
    private static String checkHeadType(int i, Cell cell) {
        if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
            throw new RuntimeException("列" + (i + 1) + ",标题不是字符:type" + cell.getCellType());
        }
        String content = cell.getStringCellValue();
        if (StringUtils.isEmpty(content)) {
            throw new NullPointerException("列" + (i + 1) + ",标题不能为空");
        }
        return content;
    }

    private static Workbook getWorkbook(InputStream inputStream, String suffix) {
        try {
            Workbook workbook;
            if (EXCEL_XLS.equalsIgnoreCase(suffix)) {
                workbook = new HSSFWorkbook(inputStream);
            } else if (EXCEL_XLSX.equalsIgnoreCase(suffix)) {
                workbook = new XSSFWorkbook(inputStream);
            } else {
                throw new Exception("未知文件类型：" + suffix);
            }
            return workbook;
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage(), e);
        }
    }

    public static final Sheet readSheet(InputStream inputStream, int sheetIndex, String suffix) {
        Workbook workbook;
        try {
            workbook = getWorkbook(inputStream, suffix);
        } catch (Exception e) {
            throw new RuntimeException("解析excel失败", e);
        }
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        return sheet;
    }

    public static final Sheet readSheet(MultipartFile file, int sheetIndex) {
        Workbook workbook;
        try {
            workbook = getWorkbook(file.getInputStream(), getFileSuffix(file));
        } catch (Exception e) {
            throw new RuntimeException("解析excel失败", e);
        }
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        return sheet;
    }

    public static final Sheet readSheet(File file, int sheetIndex) {
        try {
            FileInputStream fis = new FileInputStream(file);
            String fileName = file.getName();
            String suffix = fileName.substring(fileName.lastIndexOf("."));
            return readSheet(fis, sheetIndex, suffix);
        } catch (Exception e) {
            throw new RuntimeException("解析excel失败", e);
        }
    }

    public static final Sheet readSheet(String pathAndName, int sheetIndex) {
        File file = new File(pathAndName);
        return readSheet(file, sheetIndex);
    }


    public static final List<List<String>> readSheetDataList(InputStream inputStream, String fileSuffix, Integer sheetIndex) {
        if (sheetIndex == null) {
            sheetIndex = 0;
        }
        Workbook workbook = null;
        List<List<String>> result = new ArrayList<>();
        try {
            workbook = getWorkbook(inputStream, fileSuffix);
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            int rowSize = sheet.getPhysicalNumberOfRows();
            for (int i = 1; i < rowSize; i++) {
                List<String> rowList = new ArrayList<>();
                Row row = sheet.getRow(i);
                if (row != null) {
                    int cellSize = row.getLastCellNum();
                    for (int j = 0; j < cellSize; j++) {
                        Cell cell = row.getCell(j);
//                        String value = getValue(cell, "string");
                        String value = getCellValueByCell(cell);
                        rowList.add(value);
                    }
                    result.add(rowList);
                }
            }
        } catch (Exception e) {
            logger.error("读取excel失败", e);
            throw new RuntimeException("读取excel失败", e);
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    logger.error(e.getMessage(), e);
                }
            }
        }
        return result;
    }

    /**
     * JXL 1997-2003
     * 从excel文件中读取内容 以第一行为key，从第二行开始为正式数据
     *
     * @param file
     * @param sheetIndex
     * @return list<map<标题,值>>
     */
    public static final List<List<String>> readSheetDataList(MultipartFile file, Integer sheetIndex) {
        try {
            String fileSuffix = getFileSuffix(file);
            InputStream inputStream = file.getInputStream();
            return readSheetDataList(inputStream, fileSuffix, sheetIndex);
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage(), e);
        }
    }

    public static final List<List<String>> readSheetDataList(File file, Integer sheetIndex) {
        try {
            String fileSuffix = getFileSuffix(file);
            FileInputStream inputStream = new FileInputStream(file);
            return readSheetDataList(inputStream, fileSuffix, sheetIndex);
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage(), e);
        }
    }


    private static String getFileSuffix(MultipartFile file) {
        String file_name = file.getOriginalFilename();
        return file_name.substring(file_name.lastIndexOf("."));
    }

    private static String getFileSuffix(File file) {
        String fileName = file.getName();
        return fileName.substring(fileName.lastIndexOf("."));
    }

    public static <T> T getValue(Cell cell, String type) {
        Object value = null;
        switch (type) {
            case "int":
                Double doubleValue = cell.getNumericCellValue();
                if (doubleValue == null) {
                    doubleValue = 0.0;
                }
                String stringValue = doubleValue.toString();
                stringValue = stringValue.substring(0, stringValue.lastIndexOf("."));
                value = Integer.parseInt(stringValue);
                break;
            case "date":
                value = cell.getDateCellValue();
                break;
            case "double":
                value = cell.getNumericCellValue();
                break;
            case "string":
                try {
                    value = cell.getStringCellValue();
                } catch (Exception e) {
                    value = String.valueOf(cell.getNumericCellValue());
                }
                if (org.springframework.util.StringUtils.isEmpty(value)) {
                    value = cell.getRichStringCellValue().getString();
                }
                break;
            case "boolean":
                value = cell.getBooleanCellValue();
                break;
        }
        return (T) value;
    }


    //获取单元格各类型值，返回字符串类型
    private static String getCellValueByCell(Cell cell) {
        //判断是否为null或空串
        if (cell == null || cell.toString().trim().equals("")) {
            return "";
        }
        FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
        String cellValue = "";
        int cellType = cell.getCellType();
        if (cellType == Cell.CELL_TYPE_FORMULA) { //表达式类型
            cellType = evaluator.evaluate(cell).getCellType();
        }

        switch (cellType) {
            case Cell.CELL_TYPE_STRING: //字符串类型
                cellValue = cell.getStringCellValue().trim();
                cellValue = StringUtils.isEmpty(cellValue) ? "" : cellValue;
                break;
            case Cell.CELL_TYPE_BOOLEAN:  //布尔类型
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_NUMERIC: //数值类型
                if (HSSFDateUtil.isCellDateFormatted(cell)) {  //判断日期类型
                    cellValue = DateUtils.date2String(cell.getDateCellValue(), CommConst.DATETIME_PATTERN);
                } else {  //否
                    cellValue = new DecimalFormat("#.######").format(cell.getNumericCellValue());
                }
                break;
            default: //其它类型，取空串吧
                cellValue = "";
                break;
        }
        return cellValue;
    }

}
